﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Common.Library.Helper
{
    /// <summary>
    /// SQL Server 访问助手类 ado
    /// </summary>
    public abstract class SqlServerHelper
    {
        /********************************************
        * MS SQL 连接字符串 
        <connectionStrings>
           <add name="MSSConnectionString" connectionString="Data Source=服务器地址;Initial Catalog=数据库名称;User ID=用户名;Password=密码;min pool size=1;max pool size=200"></add>
        </connectionStrings>
        ***************************************/
        #region ExecuteNonQuery 使用所提供的参数与数据库连接字符串执行SqlCommand(不返回结果集)。
        /// <summary>
        /// 执行不包含select的数据库查询。返回受影响的行数
        /// Execute a database query which does not include a select
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="commandText">SQL命令文本</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string connectionString, string commandText)
        {
            return ExecuteNonQuery(connectionString, commandText, null);
        }

        /// <summary>
        /// 执行不包含select的数据库查询。返回受影响的行数
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="commandText">SQL命令文本</param>
        /// <param name="commandParameters">SQL命令参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string connectionString, string commandText, SqlParameter[] commandParameters)
        {
            return ExecuteNonQuery(connectionString, CommandType.Text, commandText, commandParameters);
        }

        /// <summary>
        /// 执行不包含select的数据库查询。返回受影响的行数
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="commandType">SQL命令类型</param>
        /// <param name="commandText">SQL命令文本</param>
        /// <param name="commandParameters">SQL命令参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, SqlParameter[] commandParameters)
        {
            SqlCommand command = new SqlCommand();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                PrepareCommand(command, connection, null, commandType, commandText, commandParameters);
                int value = command.ExecuteNonQuery();
                command.Parameters.Clear();
                return value;
            }
        }

        /// <summary>
        ///  使用所提供的参数与数据库连接字符串执行SqlCommand(返回受影响的行数)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="commandType">SQL命令类型</param>
        /// <param name="commandText">SQL命令文本</param>
        /// <param name="commandParameters">SQL命令参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, SqlParameter[] commandParameters)
        {
            SqlCommand command = new SqlCommand();
            PrepareCommand(command, connection, null, commandType, commandText, commandParameters);
            int value = command.ExecuteNonQuery();
            command.Parameters.Clear();
            return value;
        }
        #endregion

        #region ExecuteNonQuery 使用所提供的参数与现有的数据库事务执行SqlCommand(不返回结果集)。
        /// <summary>
        /// 使用所提供的参数与现有的数据库事务执行SqlCommand(返回受影响的行数)。
        /// </summary>
        /// <param name="transaction">数据库事务</param>
        /// <param name="commandText">SQL命令文本</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(SqlTransaction transaction, string commandText)
        {
            return ExecuteNonQuery(transaction, commandText, null);
        }

        /// <summary>
        /// 使用所提供的参数与现有的数据库事务执行SqlCommand(返回受影响的行数)。
        /// </summary>
        /// <param name="transaction">数据库事务</param>
        /// <param name="commandText">SQL命令文本</param>
        /// <param name="commandParameters">SQL命令参数</param>
        /// <returns></returns>

        public static int ExecuteNonQuery(SqlTransaction transaction, string commandText, SqlParameter[] commandParameters)
        {
            return ExecuteNonQuery(transaction, CommandType.Text, commandText, commandParameters);
        }

        /// <summary>
        /// 使用所提供的参数与现有的数据库事务执行SqlCommand(返回受影响的行数)。
        /// </summary>
        /// <param name="transaction">数据库事务</param>
        /// <param name="commandType">SQL命令类型</param>
        /// <param name="commandText">SQL命令文本</param>
        /// <param name="commandParameters">SQL命令参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters)
        {
            SqlCommand command = new SqlCommand();
            PrepareCommand(command, transaction.Connection, transaction, commandType, commandText, commandParameters);
            int value = command.ExecuteNonQuery();
            command.Parameters.Clear();
            return value;
        }
        #endregion

        #region ExecuteReader 执行一个针对现有数据库连接的select查询(该查询将返回结果集)。

        /// <summary>
        /// 执行将返回结果集的select查询。
        /// </summary>
        /// <param name="connectionString">SQL连接字符串</param>
        /// <param name="commandText">SQL命令文本</param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string connectionString, string commandText)
        {
            return ExecuteReader(connectionString, commandText, null);
        }

        /// <summary>
        /// 执行将返回结果集的select查询。
        /// </summary>
        /// <param name="connectionString">SQL连接字符串</param>
        /// <param name="commandText">SQL命令文本</param>
        /// <param name="commandParameters">SQL命令参数</param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string connectionString, string commandText, SqlParameter[] commandParameters)
        {
            return ExecuteReader(connectionString, CommandType.Text, commandText, commandParameters);
        }

        /// <summary>
        /// 执行将返回结果集的select查询。
        /// </summary>
        /// <param name="connectionString">SQL连接字符串</param>
        /// <param name="commandType">SQL命令类型</param>
        /// <param name="commandText">SQL命令文本</param>
        /// <param name="commandParameters">SQL命令参数</param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, SqlParameter[] commandParameters)
        {
            SqlCommand command = new SqlCommand();
            SqlConnection connection = new SqlConnection(connectionString);
            try
            {
                PrepareCommand(command, connection, null, commandType, commandText, commandParameters);
                SqlDataReader dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
                command.Parameters.Clear();
                return dataReader;
            }
            catch
            {
                connection.Close();
                throw;
            }
        }

        /// <summary>
        /// 执行一个针对现有数据库连接的select查询(该查询将返回结果集)。
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="commandType">SQL命令类型</param>
        /// <param name="commandText">SQL命令文本</param>
        /// <param name="commandParameters">SQL命令参数</param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, SqlParameter[] commandParameters)
        {
            SqlCommand command = new SqlCommand();
            PrepareCommand(command, connection, null, commandType, commandText, commandParameters);
            SqlDataReader dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
            command.Parameters.Clear();
            return dataReader;
        }
        #endregion

        #region ExecuteReader 使用所提供的参数与现有的数据库事务执行一个select查询(该查询将返回一个结果集)


        /// <summary>
        /// 使用所提供的参数与现有的数据库事务执行一个select查询(该查询将返回一个结果集,SQL文本命令)
        /// </summary>
        /// <param name="transaction">数据库事务</param>
        /// <param name="commandText">SQL命令文本</param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(SqlTransaction transaction, string commandText)
        {
            return ExecuteReader(transaction, commandText, null);
        }

        /// <summary>
        /// 使用所提供的参数与现有的数据库事务执行一个select查询(该查询将返回一个结果集,SQL文本命令)。
        /// </summary>
        /// <param name="transaction">数据库事务</param>
        /// <param name="commandText">SQL命令文本</param>
        /// <param name="commandParameters">SQL命令参数</param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(SqlTransaction transaction, string commandText, SqlParameter[] commandParameters)
        {
            return ExecuteReader(transaction, CommandType.Text, commandText, commandParameters);
        }

        /// <summary>
        /// 使用所提供的参数与现有的数据库事务执行一个select查询(该查询将返回一个结果集)。
        /// </summary>
        /// <param name="transaction">数据库事务</param>
        /// <param name="commandType">SQL命令类型</param>
        /// <param name="commandText">SQL命令文本</param>
        /// <param name="commandParameters">SQL命令参数</param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters)
        {
            SqlCommand command = new SqlCommand();
            PrepareCommand(command, transaction.Connection, transaction, commandType, commandText, commandParameters);
            SqlDataReader dataReader = command.ExecuteReader();
            command.Parameters.Clear();
            return dataReader;
        }
        #endregion

        #region ExecuteScalar 执行一个SqlCommand，它使用所提供的参数返回第一个记录的第一列，而不是现有的数据库连接。

        /// <summary>
        ///  执行一个SqlCommand，它使用所提供的参数返回第一个记录的第一列，而不是现有的数据库连接。
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="commandText">SQL命令文本</param>
        /// <returns></returns>
        public static object ExecuteScalar(string connectionString, string commandText)
        {
            return ExecuteScalar(connectionString, commandText, null);
        }

        /// <summary>
        /// 执行一个SqlCommand，它使用所提供的参数返回与连接字符串中指定的数据库的第一个记录的第一列。
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="commandText">SQL命令文本</param>
        /// <param name="commandParameters">SQL命令参数</param>
        /// <returns></returns>
        public static object ExecuteScalar(string connectionString, string commandText, SqlParameter[] commandParameters)
        {
            return ExecuteScalar(connectionString, CommandType.Text, commandText, commandParameters);
        }

        /// <summary>
        /// 执行一个SqlCommand，该SqlCommand将使用所提供的参数返回第一个记录的第一列。
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="commandType">SQL命令类型</param>
        /// <param name="commandText">SQL命令文本</param>
        /// <param name="commandParameters">SQL命令参数</param>
        /// <returns></returns>
        public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, SqlParameter[] commandParameters)
        {
            SqlCommand command = new SqlCommand();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                PrepareCommand(command, connection, null, commandType, commandText, commandParameters);
                object value = command.ExecuteScalar();
                command.Parameters.Clear();
                return value;
            }
        }

        /// <summary>
        /// 执行一个SqlCommand，它使用所提供的参数返回第一个记录的第一列。
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="commandType">SQL命令类型</param>
        /// <param name="commandText">SQL命令文本</param>
        /// <param name="commandParameters">SQL命令参数</param>
        /// <returns></returns>
        public static object ExecuteScalar(SqlConnection connectionString, CommandType commandType, string commandText, SqlParameter[] commandParameters)
        {
            SqlCommand command = new SqlCommand();
            PrepareCommand(command, connectionString, null, commandType, commandText, commandParameters);
            object value = command.ExecuteScalar();
            command.Parameters.Clear();
            return value;
        }
        #endregion

        #region ExcuteScalar 使用所提供的参数对指定的SqlTransaction执行一个SqlCommand(它返回一个1x1的结果集)。
        /// <summary>
        /// 使用所提供的参数对指定的SqlTransaction执行一个SqlCommand(它返回一个1x1的结果集)。
        /// </summary>
        /// <param name="transaction">数据库事务</param>
        /// <param name="commandText">SQL命令文本</param>
        /// <returns></returns>
        public static object ExecuteScalar(SqlTransaction transaction, string commandText)
        {
            return ExecuteScalar(transaction, commandText, null);
        }

        /// <summary>
        /// 使用所提供的参数对指定的SqlTransaction执行一个SqlCommand(它返回一个1x1的结果集)。
        /// </summary>
        /// <param name="transaction">数据库事务</param>
        /// <param name="commandText">SQL命令文本</param>
        /// <param name="commandParameters">SQL命令参数</param>
        /// <returns></returns>
        public static object ExecuteScalar(SqlTransaction transaction, string commandText, SqlParameter[] commandParameters)
        {
            return ExecuteScalar(transaction, CommandType.Text, commandText, commandParameters);
        }

        /// <summary>
        /// 使用所提供的参数对指定的SqlTransaction执行一个SqlCommand(它返回一个1x1的结果集)。
        /// </summary>
        /// <param name="transaction">数据库事务</param>
        /// <param name="commandType">SQL命令类型</param>
        /// <param name="commandText">SQL命令文本</param>
        /// <param name="commandParameters">SQL命令参数</param>
        /// <returns></returns>
        public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters)
        {
            SqlCommand command = new SqlCommand();
            PrepareCommand(command, transaction.Connection, transaction, commandType, commandText, commandParameters);
            object retval = command.ExecuteScalar();
            command.Parameters.Clear();
            return retval;
        }
        #endregion

        #region ExecuteDataSet 使用所提供的参数执行一个SqlCommand(返回结果集)，而不是指定的SqlConnection。

        /// <summary>
        /// 使用所提供的参数执行一个SqlCommand(返回结果集)，而不是指定的SqlConnection。
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="commandText">SQL命令文本</param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(string connectionString, string commandText)
        {
            return ExecuteDataSet(connectionString, commandText, (SqlParameter[])null);
        }

        /// <summary>
        /// 使用所提供的参数执行一个SqlCommand(返回结果集)，而不是指定的SqlConnection。
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="commandText">SQL命令文本</param>
        /// <param name="parameters">SQL命令参数</param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(string connectionString, string commandText, SqlParameter[] parameters)
        {
            return ExecuteDataSet(connectionString, CommandType.Text, commandText, parameters);
        }

        /// <summary>
        /// 使用所提供的参数执行一个SqlCommand(返回结果集)，而不是指定的SqlConnection。
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="commandType">SQL命令类型</param>
        /// <param name="commandText">SQL命令文本</param>
        /// <param name="commandParameters">SQL命令参数</param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(string connectionString, CommandType commandType, string commandText, SqlParameter[] commandParameters)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(command, connection, null, commandType, commandText, commandParameters);
                        using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
                        {
                            DataSet dataSet = new DataSet();
                            dataAdapter.Fill(dataSet);
                            command.Parameters.Clear();
                            return dataSet;
                        }
                    }
                    catch
                    {
                        connection.Close();
                        throw;
                    }
                }
            }
        }
        #endregion

        #region ExecuteDataSet 使用所提供的参数与现有的数据库连接执行一个SqlCommand(返回结果集)。

        /// <summary>
        /// 使用所提供的参数与现有的数据库连接执行一个SqlCommand(返回结果集)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="commandType">SQL命令类型</param>
        /// <param name="commandText">SQL命令文本</param>
        /// <param name="commandParameters">SQL命令参数</param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(SqlConnection connection, CommandType commandType, string commandText, SqlParameter[] commandParameters)
        {
            using (SqlCommand command = new SqlCommand())
            {
                PrepareCommand(command, connection, null, commandType, commandText, commandParameters);
                using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
                {
                    DataSet dataSet = new DataSet();
                    dataAdapter.Fill(dataSet);
                    command.Parameters.Clear();
                    return dataSet;
                }
            }
        }
        #endregion

        #region ExecuteDataSet 使用所提供的参数对指定的SqlTransaction执行一个SqlCommand(返回结果集)。
        /// <summary>
        /// 使用所提供的参数对指定的SqlTransaction执行一个SqlCommand(返回结果集)。
        /// </summary>
        /// <param name="transaction">数据库事务</param>
        /// <param name="commandText">SQL命令文本</param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(SqlTransaction transaction, string commandText)
        {
            return ExecuteDataSet(transaction, CommandType.Text, commandText, null);
        }


        /// <summary>
        /// 使用所提供的参数对指定的SqlTransaction执行一个SqlCommand(返回结果集)。
        /// </summary>
        /// <param name="transaction">数据库事务</param>
        /// <param name="commandText">SQL命令文本</param>
        /// <param name="commandParameters">SQL命令参数</param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(SqlTransaction transaction, string commandText, SqlParameter[] commandParameters)
        {
            return ExecuteDataSet(transaction, CommandType.Text, commandText, commandParameters);
        }

        /// <summary>
        /// 使用所提供的参数对指定的SqlTransaction执行一个SqlCommand(返回结果集)。
        /// </summary>
        /// <param name="transaction">数据库事务</param>
        /// <param name="commandType">SQL命令类型</param>
        /// <param name="commandText">SQL命令文本</param>
        /// <param name="commandParameters">SQL命令参数</param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters)
        {
            SqlCommand command = new SqlCommand();
            PrepareCommand(command, transaction.Connection, transaction, commandType, commandText, commandParameters);
            using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
            {
                DataSet dataSet = new DataSet();
                dataAdapter.Fill(dataSet);
                command.Parameters.Clear();
                return dataSet;
            }
        }
        #endregion

        #region MakeParams
        /// <summary>
        /// 生成输入命令参数
        /// Make input commandParameters.
        /// </summary>
        /// <commandParameters name="ParamName">commandParameters的名字</commandParameters>
        /// <commandParameters name="DbType">commandParameters的类型.</commandParameters>
        /// <commandParameters name="Size">commandParameters的大小.</commandParameters>
        /// <commandParameters name="Value">commandParameters的值.</commandParameters>
        /// <returns>New parameter.</returns>
        public static SqlParameter MakeInParam(string paramName, SqlDbType dbType, int size, object value)
        {
            return MakeParam(paramName, dbType, size, ParameterDirection.Input, value);
        }

        /// <summary>
        /// 生成输入命令参数
        /// Make input commandParameters.
        /// </summary>
        /// <commandParameters name="ParamName">commandParameters的名字</commandParameters>
        /// <commandParameters name="DbType">commandParameters的类型.</commandParameters>
        /// <commandParameters name="Size">commandParameters的大小.</commandParameters>
        /// <returns>New parameter.</returns>
        public static SqlParameter MakeOutParam(string paramName, SqlDbType dbType, int Size)
        {
            return MakeParam(paramName, dbType, Size, ParameterDirection.Output, null);
        }

        /// <summary>
        /// 生成存储过程命令参数
        /// Make stored procedure commandParameters.
        /// </summary>
        /// <commandParameters name="ParamName">commandParameters的名字.</commandParameters>
        /// <commandParameters name="DbType">commandParameters的类型.</commandParameters>
        /// <commandParameters name="Size">commandParameters的大小.</commandParameters>
        /// <commandParameters name="Direction">参数的方向.</commandParameters>
        /// <commandParameters name="Value">commandParameters的值.</commandParameters>
        /// <returns>New parameter.</returns>
        public static SqlParameter MakeParam(string paramName, SqlDbType dbType, Int32 size, ParameterDirection direction, object value)
        {
            SqlParameter commandParameters;

            if (size > 0)
            {
                commandParameters = new SqlParameter(paramName, dbType, size);
            }
            else
            {
                commandParameters = new SqlParameter(paramName, dbType);
            }

            commandParameters.Direction = direction;

            if (!(direction == ParameterDirection.Output))
            {
                if (value == null)
                {
                    value = DBNull.Value;
                }
                commandParameters.Value = value;
            }

            return commandParameters;
        }
        #endregion

        /// <summary>
        /// 准备由数据库执行的命令的内部函数。
        /// </summary>
        /// <commandParameters name="command">现有的命令对象</commandParameters>
        /// <commandParameters name="connection">数据库连接对象</commandParameters>
        /// <commandParameters name="transaction">可选的事务对象，不使用事务赋值为null</commandParameters>
        /// <commandParameters name="commandType">命令类型，例如存储过程。</commandParameters>
        /// <commandParameters name="commandText">命令文本</commandParameters>
        /// <commandParameters name="commandParameters">命令参数</commandParameters>
        private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters)
        {
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            command.Connection = connection;
            command.CommandText = commandText;
            command.CommandType = commandType;

            if (transaction != null)
            {
                command.Transaction = transaction;
            }

            if (commandParameters != null)
            {
                foreach (SqlParameter parameter in commandParameters)
                {
                    command.Parameters.Add(parameter);
                }
            }
        }

        /// <summary>
        /// 测试数据库连接状态
        /// </summary>
        /// <param name="strConnection">数据库连接字符串</param>
        /// <returns></returns>
        public static bool TestConnection(string strConnection)
        {
            if (!strConnection.ToUpper().Contains("Connection Timeout".ToUpper()))
            {
                strConnection += ";Connection Timeout=3";
            }
            return TestConnection(new SqlConnection(strConnection));
        }

        /// <summary>
        /// 测试数据库连接状态
        /// </summary>
        /// <param name="sqlConnection">数据库连接</param>
        /// <returns></returns>
        public static bool TestConnection(SqlConnection sqlConnection)
        {
            bool bRes = false;
            try
            {
                sqlConnection.Open();
                bRes = true;
            }
            catch (Exception ex)
            {
                throw new Exception("数据库连接不成功！" + ex.Message);
            }
            finally
            {
                sqlConnection.Close();
            }
            return bRes;
        }


        /// <summary>
        /// 获取连接的所有数据库名称
        /// </summary>
        /// <returns></returns>
        public static ICollection<string> GetConnectionAllDataBaseName(SqlConnection sqlConnection)
        {
            ICollection<string> listDataBase = new List<string>();
            try
            {
                DataSet dsDataBase = new DataSet();
                dsDataBase = ExecuteDataSet(sqlConnection.ConnectionString, @"select name,crdate from master..sysdatabases");
                if (dsDataBase != null && dsDataBase.Tables.Count > 0)
                {
                    foreach (DataRow dr in dsDataBase.Tables[0].Rows)
                    {
                        listDataBase.Add(dr["name"].ToString());
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception("读取[" + sqlConnection.ConnectionString + "]连接中的数据库出错！" + ex.ToString());
            }
            return listDataBase;
        }

        /// <summary>
        /// 获取连接的所有数据库名称
        /// </summary>
        /// <returns></returns>
        public static ICollection<string> GetConnectionAllDataBaseName(string strConnection)
        {
            return GetConnectionAllDataBaseName(new SqlConnection(strConnection));
        }

        public static string ParameterValueForSQL(SqlParameter sp)
        {
            String retval = "";

            switch (sp.SqlDbType)
            {
                case SqlDbType.Char:
                case SqlDbType.NChar:
                case SqlDbType.NText:
                case SqlDbType.NVarChar:
                case SqlDbType.Text:
                case SqlDbType.Time:
                case SqlDbType.VarChar:
                case SqlDbType.Xml:
                case SqlDbType.Date:
                case SqlDbType.DateTime:
                case SqlDbType.DateTime2:
                case SqlDbType.DateTimeOffset:
                    retval = "'" + sp.Value.ToString().Replace("'", "''") + "'";
                    break;

                case SqlDbType.Bit:
                    bool b = false;
                    try
                    {
                        b = Convert.ToBoolean(sp.Value);
                    }
                    catch
                    {

                    }
                    retval = b ? "1" : "0";
                    break;

                default:
                    retval = sp.Value.ToString().Replace("'", "''");
                    break;
            }

            return retval;
        }

        public static String CommandAsSql(SqlCommand sc)
        {
            StringBuilder sql = new StringBuilder();
            Boolean FirstParam = true;

            sql.AppendLine("use " + sc.Connection.Database + ";");
            switch (sc.CommandType)
            {
                case CommandType.StoredProcedure:
                    sql.AppendLine("declare @return_value int;");

                    foreach (SqlParameter sp in sc.Parameters)
                    {
                        if ((sp.Direction == ParameterDirection.InputOutput) || (sp.Direction == ParameterDirection.Output))
                        {
                            sql.Append("declare " + sp.ParameterName + "\t" + sp.SqlDbType.ToString() + "\t= ");

                            sql.AppendLine(((sp.Direction == ParameterDirection.Output) ? "null" : ParameterValueForSQL(sp)) + ";");

                        }
                    }

                    sql.AppendLine("exec [" + sc.CommandText + "]");

                    foreach (SqlParameter sp in sc.Parameters)
                    {
                        if (sp.Direction != ParameterDirection.ReturnValue)
                        {
                            sql.Append((FirstParam) ? "\t" : "\t, ");

                            if (FirstParam) FirstParam = false;

                            if (sp.Direction == ParameterDirection.Input)
                                sql.AppendLine(sp.ParameterName + " = " + ParameterValueForSQL(sp));
                            else

                                sql.AppendLine(sp.ParameterName + " = " + sp.ParameterName + " output");
                        }
                    }
                    sql.AppendLine(";");

                    sql.AppendLine("select 'Return Value' = convert(varchar, @return_value);");

                    foreach (SqlParameter sp in sc.Parameters)
                    {
                        if ((sp.Direction == ParameterDirection.InputOutput) || (sp.Direction == ParameterDirection.Output))
                        {
                            sql.AppendLine("select '" + sp.ParameterName + "' = convert(varchar, " + sp.ParameterName + ");");
                        }
                    }
                    break;
                case CommandType.Text:
                    sql.AppendLine(sc.CommandText);
                    break;
            }

            return sql.ToString();
        }

        /// <summary>
        /// SqlParameter参数转字符串
        /// </summary>
        /// <param name="sqlParameters"></param>
        /// <returns></returns>
        public static string SqlParametersToString(SqlParameter[] sqlParameters)
        {
            StringBuilder sb = new StringBuilder();
            foreach (var item in sqlParameters)
            {
                sb.Append($"{item.ParameterName}={item.Value} {item.SqlDbType},");
            }
            return sb.ToString();
        }

        /// <summary>
        /// Sql及SqlParameter参数转字符串
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sqlParameters"></param>
        /// <returns></returns>
        public static string SqlAndSqlParameersToString(string sql, SqlParameter[] sqlParameters)
        {
            string sqlRes = "";
            foreach (var item in sqlParameters)
            {
                string objRes = "";
                if (item.SqlDbType != SqlDbType.Bit
                    && item.SqlDbType != SqlDbType.BigInt
                    && item.SqlDbType != SqlDbType.Binary
                    && item.SqlDbType != SqlDbType.Decimal
                    && item.SqlDbType != SqlDbType.Float
                    && item.SqlDbType != SqlDbType.Int
                    && item.SqlDbType != SqlDbType.Money
                    && item.SqlDbType != SqlDbType.Real
                    && item.SqlDbType != SqlDbType.SmallInt
                    && item.SqlDbType != SqlDbType.SmallMoney)
                {
                    objRes = $"'{item.Value}'";
                }
                else
                {
                    objRes = $"{item.Value}";
                }
                sqlRes = sql.Replace(item.ParameterName, objRes);
            }
            return sqlRes;
        }
    }
}
